﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Com.Rivyer.Web.ip
{
    public partial class ipoverview : System.Web.UI.Page
    {
        private readonly Com.Rivyer.Model.ipaddressModel ipmodel = new Model.ipaddressModel();
        private readonly BLL.ipaddressBll ipbll = new BLL.ipaddressBll();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void ipoverviewgrid_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e)
        {
            if (e.NewValues["usedperson"] == null
                || (e.NewValues["department"] == null && e.NewValues["organization"] == null))
            {
                e.Cancel = true;
            }
            else
            {
                Model.ipaddressModel ipmodel = new Model.ipaddressModel()
                {
                    id = int.Parse(e.NewValues["id"].ToString()),
                    usedperson = e.NewValues["usedperson"].ToString(),
                    department = e.NewValues["department"] == null ? " " : e.NewValues["department"].ToString(),
                    organization = e.NewValues["organization"] == null ? " " : e.NewValues["organization"].ToString(),
                    usedinternet = int.Parse(e.NewValues["usedinternet"].ToString()),
                    description = e.NewValues["description"] == null ? " " : e.NewValues["description"].ToString(),
                    macaddress = e.NewValues["macaddress"] == null ? " " : e.NewValues["macaddress"].ToString(),
                    modifydate = DateTime.Now,
                };
                if (e.NewValues["productdate"] != null)
                {
                    ipmodel.productdate = DateTime.Parse(e.NewValues["productdate"].ToString());
                }
                if (ipmodel.macaddress.Trim().Length != 0 && !Maticsoft.Common.PageValidate.IsMac(ipmodel.macaddress.Trim()))
                {
                    e.Cancel = true;
                }

                {
                    new BLL.useroperatelogBll().AddLog(Session["username"].ToString(), "更新", string.Format("IP地址表{0}", ipbll.GetModel(ipmodel.id).ip));
                    ipbll.Update(ipmodel);
                    e.Cancel = true;
                    ipoverviewgrid.CancelEdit();
                }
                //修改监控列表
                int test = new BLL.monitorBll().GetList(1, string.Format("ip = '{0}'", e.OldValues["ip"].ToString()), "id").Tables[0].Rows.Count;
                if (new BLL.monitorBll().GetList(1, string.Format("ip = '{0}'", e.OldValues["ip"].ToString()), "id").Tables[0].Rows.Count != 0)
                {
                    Model.monitorModel monitormodel = new Model.monitorModel();
                    monitormodel.department = ipmodel.department;
                    monitormodel.address = ipmodel.usedperson;
                    monitormodel.description = ipmodel.description;
                    monitormodel.ip = e.OldValues["ip"].ToString();
                    monitormodel.num = 1;
                    monitormodel.id = int.Parse(new BLL.monitorBll().GetList(1, string.Format("ip = '{0}'", e.OldValues["ip"].ToString()), "id").Tables[0].Rows[0]["id"].ToString());
                    new BLL.monitorBll().Update(monitormodel);
                }
            }
        }

        protected void ipoverviewgrid_RowDeleting(object sender, DevExpress.Web.Data.ASPxDataDeletingEventArgs e)
        {
            e.Cancel = true;
            Model.ipaddressModel ipmodel = new Model.ipaddressModel()
            {
                id = int.Parse(e.Values["id"].ToString()),
                ip = e.Values["ip"].ToString(),
                usedperson = " ",
                department = " ",
                organization = " ",
                usedinternet = 1,
                description = " ",
                macaddress = " ",
                productdate = DateTime.MinValue,
                modifydate = DateTime.Now,
            };
            {
                new BLL.useroperatelogBll().AddLog(Session["username"].ToString(), "清除", string.Format("IP地址:{0}", ipbll.GetModel(ipmodel.id).ip));
                ipbll.Update(ipmodel);
                if (new BLL.monitorBll().GetList(1, string.Format("ip = '{0}'", ipmodel.ip), "id").Tables[0].Rows.Count != 0)
                {
                    new BLL.monitorBll().Delete(int.Parse(new BLL.monitorBll().GetList(1, string.Format("ip = '{0}'", ipmodel.ip), "id").Tables[0].Rows[0]["id"].ToString()));
                }
            }
        }

        protected void editBatch_Click(object sender, EventArgs e)
        {
            List<object> ipselected = ipoverviewgrid.GetSelectedFieldValues("id");

            if (ipselected.Count <= 1)
            {
                Maticsoft.Common.MessageBox.Show(this, "未选中至少两行数据,无法使用批量编辑模式!");
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                foreach (int ipselect in ipselected)
                {
                    sb.Append(ipselect + ",");
                }
                sb = sb.Remove(sb.ToString().LastIndexOf(','), 1);
                Response.Redirect(string.Format("./iptouse.aspx?add={0}", sb.ToString()));
            }
        }

        protected void deleteBatch_Click(object sender, EventArgs e)
        {
            List<object> ipselected = ipoverviewgrid.GetSelectedFieldValues("id");

            if (ipselected.Count <= 1)
            {
                Maticsoft.Common.MessageBox.Show(this, "未选中至少两行数据,无法使用批量清除模式!");
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                foreach (int ipselect in ipselected)
                {
                    sb.Append(ipselect + ",");
                }
                sb = sb.Remove(sb.ToString().LastIndexOf(','), 1);
                Response.Redirect(string.Format("./iptodelete.aspx?delete={0}", sb.ToString()));
            }
        }

        protected void pingbtn_Click(object sender, EventArgs e)
        {
            List<object> ipselected = ipoverviewgrid.GetSelectedFieldValues("id");

            if (ipselected.Count == 0)
            {
                Maticsoft.Common.MessageBox.Show(this, "未选中一行数据!");
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                foreach (int ipselect in ipselected)
                {
                    sb.Append(ipselect + ",");
                }
                sb = sb.Remove(sb.ToString().LastIndexOf(','), 1);
                Response.Redirect(string.Format("./ping.aspx?ping={0}", sb.ToString()));
            }
        }

        protected void macbtn_Click(object sender, EventArgs e)
        {
            List<object> ipselected = ipoverviewgrid.GetSelectedFieldValues("id");

            if (ipselected.Count == 0)
            {
                Maticsoft.Common.MessageBox.Show(this, "未选中一行数据!");
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                foreach (int ipselect in ipselected)
                {
                    sb.Append(ipselect + ",");
                }
                sb = sb.Remove(sb.ToString().LastIndexOf(','), 1);
                Response.Redirect(string.Format("./ping.aspx?mac={0}", sb.ToString()));
            }
        }

        protected void ASPxButton1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
            {
                Response.Write("<script>alert('请您选择Excel文件')</script> ");
                return;//当无文件时,返回
            }
            string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
            if (IsXls != ".xls")
            {
                Response.Write("<script>alert('只可以选择Excel文件')</script>");
                return;//当选择的不是Excel文件时,返回
            }
            string filename = FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
            string finalfilename = DateTime.Now.Year + "年" + DateTime.Now.Month + "月" + DateTime.Now.Day + "日" + DateTime.Now.Hour + "点" + DateTime.Now.Minute + "分" + Session["realname"].ToString() + filename;
            string savePath = Server.MapPath(("upload\\") + finalfilename);//Server.MapPath 获得虚拟服务器相对路径
            FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上
            DataSet ds = ExcelSqlConnection(savePath, finalfilename);           //调用自定义方法
            DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
            int rowsnum = ds.Tables[0].Rows.Count;
            if (rowsnum == 0)
            {
                Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
            }
            else
            {
                for (int i = 0; i < dr.Length; i++)
                {
                    DateTime dt;
                    int test;
                    //需要做的工作
                    //前面除了你需要在建立一个“upfiles”的文件夹外，其他的都不用管了，你只需要通过下面的方式获取Excel的值，然后再将这些值用你的方式去插入到数据库里面
                    ipmodel.ip = dr[i]["ip地址"].ToString().Trim();
                    if (Maticsoft.Common.PageValidate.IsIp(ipmodel.ip))
                    {
                        ipmodel.department = dr[i]["部门"].ToString().Trim();
                        ipmodel.description = dr[i]["备注"].ToString().Trim();
                        ipmodel.macaddress = dr[i]["mac地址"].ToString().Trim();
                        ipmodel.modifydate = DateTime.Now;
                        ipmodel.organization = dr[i]["单位"].ToString().Trim();
                        if (DateTime.TryParse(dr[i]["生产日期"].ToString().Trim(), out dt))
                        {
                            ipmodel.productdate = dt;
                        }
                        else
                        {
                            ipmodel.productdate = DateTime.MinValue;
                        }
                        if (dr[i]["互联网是否"].ToString().Trim() == "是")
                        {
                            ipmodel.usedinternet = 1;
                        }
                        else
                        {
                            ipmodel.usedinternet = 0;
                        }
                        ipmodel.usedperson = dr[i]["责任人"].ToString().Trim();

                        //查找这个IP看数据库中是否存在,存在就是修改数据
                        if (ipbll.GetList(1, string.Format("ip = '{0}'", ipmodel.ip), "id").Tables[0].Rows.Count == 1)
                        {
                            ipmodel.id = int.Parse(ipbll.GetList(1, string.Format("ip = '{0}'", ipmodel.ip), "id").Tables[0].Rows[0]["id"].ToString());
                            ipbll.Update(ipmodel);
                        }
                    }
                    //Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
                }
                Response.Write("<script>alert('Excle表导入成功!');</script>");

            }
        }

        #region 连接Excel  读取Excel数据   并返回DataSet数据集合
        /// <summary>
        /// 连接Excel  读取Excel数据   并返回DataSet数据集合
        /// </summary>
        /// <param name="filepath">Excel服务器路径</param>
        /// <param name="tableName">Excel表名称</param>
        /// <returns></returns>
        public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
        {
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection ExcelConn = new OleDbConnection(strCon);
            try
            {
                string strCom = string.Format("SELECT * FROM [Sheet1$]");
                ExcelConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
                DataSet ds = new DataSet();
                myCommand.Fill(ds, "[" + tableName + "$]");
                ExcelConn.Close();
                return ds;
            }
            catch
            {
                ExcelConn.Close();
                return null;
            }
        }
        #endregion
    }
}